package fisio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import fisio.connection.ConnectionManager;
import fisio.global.Util;
import fisio.model.Fisioterapeuta;

public class FisioterapeutaDAO {

	private static final String SQL_AUTENTICAR_USUARIO = "SELECT * FROM fisioterapeuta WHERE cpf = ? AND senha = ?";
	private static final String SQL_INSERT_FISIOTERAPEUTA = "INSERT INTO fisioterapeuta (cpf, crefito, nome, senha, email, celular, formacao, perfil_admin) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_DELETE_FISIOTERAPEUTA = "DELETE FROM fisioterapeuta WHERE id_f = ?";
	private static final String SQL_UPDATE_FISIOTERAPEUTA = "UPDATE fisioterapeuta SET cpf = ?, crefito = ?, nome = ?, senha = ?, email = ?, celular = ?, formacao = ?, perfil_admin = ? WHERE id_f = ?";
	private static final String SQL_UPDATE_USUARIO = "UPDATE fisioterapeuta SET cpf = ?, crefito = ?, nome = ?, email = ?, celular = ?, formacao = ? WHERE id_f = ?";
	private static final String SQL_GET_FISIOTERAPEUTA_BY_ID = "SELECT * FROM fisioterapeuta WHERE id_f = ?";
	private static final String SQL_GET_FISIOTERAPEUTAS_COMPLETO = "SELECT * FROM fisioterapeuta ORDER BY nome";
	private static final String SQL_GET_FISIOTERAPEUTAS_EXCETO = "SELECT id_f, cpf, crefito, nome FROM fisioterapeuta WHERE id_f <> ? ORDER BY nome";
	private static final String SQL_GET_FISIOTERAPEUTAS_ID_NOME = "SELECT id_f, nome FROM fisioterapeuta ORDER BY nome";
	private static final String SQL_ALTERAR_SENHA = "UPDATE fisioterapeuta SET senha = ? WHERE cpf = ?";
	
	public static Fisioterapeuta autenticar(String cpf, String senha) {

		Connection conn = ConnectionManager.getConnection();
		Fisioterapeuta user = null;

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_AUTENTICAR_USUARIO);
			stmt.setString(1, Util.unmaskCPF(cpf));
			stmt.setString(2, senha);
			
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				user = new Fisioterapeuta(
									rs.getInt(1),
									rs.getString(3),
									rs.getString(4),
									rs.getString(5),
									rs.getString(6),
									rs.getString(7),
									rs.getString(8),
									rs.getString(9),
									rs.getString(2),//foto
									rs.getInt(10)==1?true:false
								);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na autenticacao: "+e.getMessage());
		}

		return user;
	}
	
	public static void alterarSenha(String cpf, String senhaNova) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_ALTERAR_SENHA);
			stmt.setString(1, senhaNova);
			stmt.setString(2, cpf);
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO ao alterar senha: "+e.getMessage());
		}
	}
	
	public static void insertFisioterapeuta(Fisioterapeuta fisioterapeuta) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_INSERT_FISIOTERAPEUTA);
			stmt.setString(1, fisioterapeuta.getCpf());
			stmt.setString(2, fisioterapeuta.getCrefito());
			stmt.setString(3, fisioterapeuta.getNome());
			stmt.setString(4, fisioterapeuta.getSenha());
			stmt.setString(5, fisioterapeuta.getEmail());
			stmt.setString(6, fisioterapeuta.getCelular());
			stmt.setString(7, fisioterapeuta.getFormacao());
			stmt.setInt(8, fisioterapeuta.temPerfilAdmin()?1:0);
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na insercao: "+e.getMessage());
		}
	}

	public static void deleteFisioterapeuta(int id) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_DELETE_FISIOTERAPEUTA);
			stmt.setInt(1, id);
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na remocao: "+e.getMessage());
		}
	}
	
	public static void updateFisioterapeuta(int id, Fisioterapeuta fisioterapeuta) {
		
		Connection conn = ConnectionManager.getConnection();
		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_UPDATE_FISIOTERAPEUTA);
			stmt.setString(1, fisioterapeuta.getCpf());
			stmt.setString(2, fisioterapeuta.getCrefito());
			stmt.setString(3, fisioterapeuta.getNome());
			stmt.setString(4, fisioterapeuta.getSenha());
			stmt.setString(5, fisioterapeuta.getEmail());
			stmt.setString(6, fisioterapeuta.getCelular());
			stmt.setString(7, fisioterapeuta.getFormacao());
			stmt.setInt(8, fisioterapeuta.temPerfilAdmin()?1:0);
			stmt.setInt(9, id);
			
			stmt.executeUpdate();

			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na edicao: "+e.getMessage());
		}
	}
	
	public static void updateUsuario(int id, Fisioterapeuta fisioterapeuta) {
		
		Connection conn = ConnectionManager.getConnection();
		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_UPDATE_USUARIO);
			stmt.setString(1, fisioterapeuta.getCpf());
			stmt.setString(2, fisioterapeuta.getCrefito());
			stmt.setString(3, fisioterapeuta.getNome());
			stmt.setString(4, fisioterapeuta.getEmail());
			stmt.setString(5, fisioterapeuta.getCelular());
			stmt.setString(6, fisioterapeuta.getFormacao());
			stmt.setInt(7, id);
			
			stmt.executeUpdate();

			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na edicao de usuario: "+e.getMessage());
		}
	}
	
	public static Fisioterapeuta getFisioterapeuta(int id) {

		Connection conn = ConnectionManager.getConnection();
		Fisioterapeuta fisio = null;

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_FISIOTERAPEUTA_BY_ID);
			stmt.setInt(1, id);
			
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				fisio = new Fisioterapeuta(
									rs.getInt(1),
									rs.getString(3),
									rs.getString(4),
									rs.getString(5),
									rs.getString(6),
									rs.getString(7),
									rs.getString(8),
									rs.getString(9),
									rs.getString(2),//foto
									rs.getInt(10)==1?true:false
								);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return fisio;
	}

	public static Vector<Fisioterapeuta> getFisioterapeutasCompleto() {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Fisioterapeuta> todos = new Vector<Fisioterapeuta>();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_FISIOTERAPEUTAS_COMPLETO);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(new Fisioterapeuta(
										rs.getInt(1),
										rs.getString(3),
										rs.getString(4),
										rs.getString(5),
										rs.getString(6),
										rs.getString(7),
										rs.getString(8),
										rs.getString(9),
										rs.getString(2),//foto
										rs.getInt(10)==1?true:false
									)
				);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return todos;
	}
	
	public static Vector<Fisioterapeuta> getFisioterapeutasParcialExceto(int idExceto) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Fisioterapeuta> todos = new Vector<Fisioterapeuta>();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_FISIOTERAPEUTAS_EXCETO);
			stmt.setInt(1, idExceto);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(new Fisioterapeuta(
										rs.getInt(1),
										rs.getString(2),
										rs.getString(3),
										rs.getString(4)
									)
				);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return todos;
	}
	
	public static Vector<Fisioterapeuta> getFisioterapeutasIdNome() {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Fisioterapeuta> todos = new Vector<Fisioterapeuta>();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_FISIOTERAPEUTAS_ID_NOME);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Fisioterapeuta temp = new Fisioterapeuta(rs.getInt(1));
				temp.setNome(rs.getString(2));
				todos.add(temp);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return todos;
	}
}
